package au.com.somethingwritten.model;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

public class OrderDAO {
	private static Connection connection = null;
	private static Statement statement = null;
	private static ResultSet resultSet = null;
	
	public static int insertOrder(Order order) throws Exception{
		int numRowsEffected = 0;
		/*
		 * General process
		 * 
		 * insert payment.
		 * get id of payment
		 * insert order.
		 * get order id
		 * insert order items.
		 * 
		 */
		
		
		try{
			//load the MySQL JDBC driver
			Class.forName("com.mysql.jdbc.Driver");
			//create the database connection
			connection = DriverManager
			.getConnection("jdbc:mysql://localhost/somethingwritten?"
			+ "user=laac&password=password");
			
			//create object for sending SQL statements
			statement = connection.createStatement();
			
			//get the payment object
			Payment tmpPayment = order.getPayment();
			
			//set date format
			java.text.SimpleDateFormat dateFormat = 
				     new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			
			//insert payment
			numRowsEffected = statement.executeUpdate("INSERT INTO payments " +
					"values (default, " +
					"'" + tmpPayment.getCardType() + "', " +
					"'" + tmpPayment.getCardNumber() + "', " +
					"'" + dateFormat.format(tmpPayment.getExpiryDate()) + "', " +
					tmpPayment.getCvv().toString() + ", " +
					"'" + tmpPayment.getCardName() + "', " +
					"'" + tmpPayment.getAddress() + "', " +
					"'" + tmpPayment.getCity() + "', " +
					"'" + tmpPayment.getState() + "', " +
					tmpPayment.getPostcode().toString() + ")");		
			
			//insert order, with id generated for payment
			numRowsEffected += statement.executeUpdate("INSERT INTO orders " +
					"values (default, " +
					"'" + dateFormat.format(order.getOrderDate()) + "', " +
					order.getShippingMethodId().toString() + ", " +
					"'" + order.getAddress() + "', " +
					"'" + order.getCity() + "', " +
					"'" + order.getState() + "', " +
					order.getPostcode().toString() + ", " +
					//payment id is retrieved from SQL database
					//by getting the last id generated in this SQL connection.
					"LAST_INSERT_ID(), " +
					"'" + order.getShippingStatus() + "', " +
					"'" + order.getGuestEmail() + "')");
			
			//insert all order items, with id generated for order
			ArrayList<CartItem> items = order.getOrderItems();
			for(int i = 0; i < items.size(); i++){
				//update book quantity in database
				
				int newQuantity = items.get(i).getItem().getQuantity() - items.get(i).getQuantity();
				
				if(newQuantity < 0)
					newQuantity = 0;
				
				items.get(i).getItem().setQuantity(newQuantity);
				BookDAO.updateBookQuantity(items.get(i).getItem());
				
				numRowsEffected += statement.executeUpdate("INSERT INTO orderitems " +
						"values (" +
						items.get(i).getItem().getId().toString() + ", " +
						//order id is retrieved from SQL database
						//by getting the last id generated in this SQL connection
						//since no auto_increment values are in the orderitems table
						//this value stays constant throughout the loop.
						"LAST_INSERT_ID(), " +
						items.get(i).getQuantity().toString() + ", " +
						items.get(i).getCost().toString() +
						")");
			}//end order item insert loop.
			
		} catch (Exception e) {
			throw e;
		} finally {
			close();
		}
		
		return numRowsEffected;
	}
	
	
	
	/***
	 * Close all connect objects.
	 */
	private static void close() {
		try {
			if (resultSet != null) {
				resultSet.close();
			}
			if (statement != null) {
				statement.close();
			}
			if (connection != null) {
				connection.close();
			}
		} catch (Exception e) {
			
		}
	}
}
